<?php
class PortfolioManagementController extends AppController {
public function index() 
{
    include("Component/connection.php");
    $this->layout='default';
    $sql="select distinct portfolioname from portfoliomanagement order by portfolioname asc";
    $sql2="select distinct name from apircodes order by name asc";
    $sql3="select distinct portfolioname from portfoliomanagement where portfolioname like '%TAA' or portfolioname like '%SAA' order by portfolioname asc";
    $portN=mysqli_query($con,$sql);
    $fundN=mysqli_query($con,$sql2);
    $rpmN=mysqli_query($con,$sql3);
    $portname=array();
    $fundname=array();
    $rpmname=array();

    foreach ($portN as $port)
    {
        foreach($port as $p)
        {
            array_push($portname,$p);
        }
    }

    foreach ($fundN as $fund)
    {
        foreach($fund as $f)
        {
            array_push($fundname,$f);
        }
    }

    foreach($rpmN as $rpm)
    {
        foreach($rpm as $r)
        {
            array_push($rpmname,$r);
        }
    }
    
    $this->set('portname',$portname);
    $this->set('fundname',$fundname);    
    $this->set('rpmname',$rpmname);
}

public function addFund()
{
    include("Component/connection.php");
    $fundname=$this->request->data['fundnametext'];
    $apir=$this->request->data['apircode'];
    $sql="insert into apircodes values ('".$apir."','".$fundname."')";
    mysqli_query($con,$sql);

    $this->redirect(array(
                    'controller'=>'PortfolioManagement',
                    'action'=>'index'
                ) );
}

public function modifyFund()
{
    include("Component/connection.php");
    $fundname=$this->request->data['selectBoxFund'];
    $apircode=$this->request->data['apircodenew'];
    $sql="update apircodes set apir='".$apircode."' where name='".$fundname."'";
    mysqli_query($con,$sql);

    $this->redirect(array(
                    'controller'=>'PortfolioManagement',
                    'action'=>'index'
                ) );
}

public function addPortfolio()
{
    include("Component/connection.php");
    set_time_limit(0);
    $name=$this->request->data['portfolionametext'];
    $dateTemp=$this->request->data['datetext1'];
    $flist=$this->request->data['fundtext1'];
    $fweighting=$this->request->data['fweightingtext1'];
    $ilist=$this->request->data['indextext1'];
    $iweighting=$this->request->data['iweightingtext1'];

    $dateExplode = explode('/',$dateTemp);
    $year=$dateExplode[2];
    $month=$dateExplode[1];
    $day=$dateExplode[0];

    $date=$year."-".$month."-".$day;
    $sql="insert into portfoliomanagement (date,portfolioname,fundlist,indexlist,fweightinglist,iweightinglist) values ('".$date."','".$name."','".$flist."','".$ilist."','".$fweighting."','".$iweighting."')";
    mysqli_query($con,$sql);

    //based on the new portfolio info, add to modelportfolios table.
    date_default_timezone_set('Australia/Melbourne');
    $date2 = date('d/m/Y', time());
    $dateExplode2 = explode('/',$date2);
    $latestyear=$dateExplode2[2];
    $latestmonth=$dateExplode2[1];

    if($year==$latestyear && $month<=$latestmonth)//same year, earlier month
    {
        $looptimes=$latestmonth-$month+1;
        for($n=0;$n<$looptimes;$n++)
        {
            $insertMonth=$month+$n;
            if($insertMonth==2){$insertDate=$year."-".$insertMonth."-"."28";}
            else{$insertDate=$year."-".$insertMonth."-"."30";}
            $sqlcheck="select count(*) from modelportfolios where portfolioname='".$name."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $resultcheck=mysqli_query($con,$sqlcheck);
            $countercheck=0;
            foreach($resultcheck as $recheck)
            {
                foreach($recheck as $rc)
                {
                    $countercheck=$rc;
                }
            }
            if($countercheck==0)
            {
                $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$flist."','".$ilist."','".$insertDate."','".$fweighting."','".$iweighting."')";
                mysqli_query($con,$sql3);
            }
        }
    }else if ($year<$latestyear)//earlier year
    {
        $yearlooptimes=$latestyear-$year+1;
        for($n=0;$n<$yearlooptimes;$n++)
        {
            $insertyear=$year+$n;
            if($n==0)
            {
                //first year
                $looptimes=12-$month+1;
                for($m=0;$m<$looptimes;$m++)
                {
                    $insertMonth=$month+$m;
                    if($insertMonth==2){$insertDate=$insertyear."-".$insertMonth."-"."28";}
                    else{$insertDate=$insertyear."-".$insertMonth."-"."30";}
                    $sqlcheck="select count(*) from modelportfolios where portfolioname='".$name."' and year(date)=year('".$insertDate."') and month(date)=month('".$insertDate."')";
                    $resultcheck=mysqli_query($con,$sqlcheck);
                    $countercheck=0;
                    foreach($resultcheck as $recheck)
                    {
                        foreach($recheck as $rc)
                        {
                            $countercheck=$rc;
                        }
                    }
                    if($countercheck==0)
                    {
                        $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$flist."','".$ilist."','".$insertDate."','".$fweighting."','".$iweighting."')";
                        mysqli_query($con,$sql3);
                    }
                }
            }
            else if ($n<$yearlooptimes-1)
            {
                // year bettween first and last year
                for($m=1;$m<=12;$m++)
                {
                    if($m==2){$insertDate=$insertyear."-".$m."-"."28";}
                    else{$insertDate=$insertyear."-".$m."-"."30";}
                    $sqlcheck="select count(*) from modelportfolios where portfolioname='".$name."' and year(date)=year('".$insertDate."') and month(date)=month('".$insertDate."')";
                    $resultcheck=mysqli_query($con,$sqlcheck);
                    $countercheck=0;
                    foreach($resultcheck as $recheck)
                    {
                        foreach($recheck as $rc)
                        {
                            $countercheck=$rc;
                        }
                    }
                    if($countercheck==0)
                    {
                        $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$flist."','".$ilist."','".$insertDate."','".$fweighting."','".$iweighting."')";
                        mysqli_query($con,$sql3);
                    }
                }
            }
            else
            {
                for($m=1;$m<=$latestmonth;$m++)
                {
                    if($m==2){$insertDate=$insertyear."-".$m."-"."28";}
                    else{$insertDate=$insertyear."-".$m."-"."30";}
                    $sqlcheck="select count(*) from modelportfolios where portfolioname='".$name."' and year(date)=year('".$insertDate."') and month(date)=month('".$insertDate."')";
                    $resultcheck=mysqli_query($con,$sqlcheck);
                    $countercheck=0;
                    foreach($resultcheck as $recheck)
                    {
                        foreach($recheck as $rc)
                        {
                            $countercheck=$rc;
                        }
                    }
                    if($countercheck==0)
                    {
                        $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$flist."','".$ilist."','".$insertDate."','".$fweighting."','".$iweighting."')";
                        mysqli_query($con,$sql3);
                    }
                }
                //last year
            }
        }
    }

    //calculation if needed
    //may need to check if there's fund info in the db
    $sql4="select date from modelportfolios where portfolioname='".$name."' and year(date) ='".$year."' and month(date)>='".$month."'";
    $sql5="select date from modelportfolios where portfolioname='".$name."' and year(date) >'".$year."'";

    $resultdate2=mysqli_query($con,$sql5);
    $resultdate=mysqli_query($con,$sql4);
    foreach ($resultdate as $resultdat)
    {
        foreach ($resultdat as $rd)
        {
            $this->calculation($name,$rd);
        }
    }

    foreach($resultdate2 as $resultdat2)
    {
        foreach ($resultdat2 as $rd2)
        {
            $this->calculation($name,$rd2);
        }
    }

    $this->redirect(array(
                    'controller'=>'PortfolioManagement',
                    'action'=>'index'
                ) );
}

public function modifyPortfolio()
{
    set_time_limit(0);
    include("Component/connection.php");
    $name=$this->request->data['selectBoxPortfolio'];
    $date=$this->request->data['datetext'];
    $flist=$this->request->data['fundtext'];
    $fweighting=$this->request->data['fweightingtext'];
    $ilist=$this->request->data['indextext'];
    $iweighting=$this->request->data['iweightingtext'];

    $dateExplode = explode('/',$date);
    $year=$dateExplode[2];
    $month=$dateExplode[1];
    $day=$dateExplode[0];

    $date=$year."-".$month."-".$day;

    //update portfoliomanagement table
    $sql="select id from portfoliomanagement where portfolioname='".$name."' and year(date)='".$year."' and month(date)>='".$month."' order by id asc";
    $sql2="select id from portfoliomanagement where portfolioname='".$name."' and year(date)>'".$year."' order by id asc";
    $result=mysqli_query($con,$sql);
    $result2=mysqli_query($con,$sql2);
    $updatedportid=array();
    if(sizeof($result)>0)
    {
        foreach($result as $resul)
        {
            foreach($resul as $r)
            {
                array_push($updatedportid,$r);
            }
        }
    }
    if(sizeof($result2)>0)
    {
        foreach($result2 as $resul)
        {
            foreach($resul as $r)
            {
                array_push($updatedportid,$r);
            }
        }
    }

    for($n=0;$n<sizeof($updatedportid);$n++)
    {
        $sql="update portfoliomanagement set fundlist='".$flist."',fweightinglist='".$fweighting."',indexlist='".$ilist."',iweightinglist='".$iweighting."' where id='".$updatedportid[$n]."'";
        mysqli_query($con,$sql);
    }
    //update portfolio table
    $sql="update modelportfolios set fundslist='".$flist."',fweightinglist='".$fweighting."',indexlist='".$ilist."',iweightinglist='".$iweighting."' where portfolioname='".$name."' and year(date)='".$year."' and month(date)>='".$month."' order by portfoliosid asc";
    $sql2="update modelportfolios set fundslist='".$flist."',fweightinglist='".$fweighting."',indexlist='".$ilist."',iweightinglist='".$iweighting."' where portfolioname='".$name."' and year(date)>'".$year."' order by portfoliosid asc";
    mysqli_query($con,$sql);
    mysqli_query($con,$sql2);

    //get modelportfolio names and dates
    $namedatearray=array();
    $sql="select portfolioname,date from modelportfolios where portfolioname='".$name."' and year(date)='".$year."' and month(date)>='".$month."' order by portfoliosid asc";
    $sql2="select portfolioname,date from modelportfolios where portfolioname='".$name."' and year(date)>'".$year."' order by portfoliosid asc";
    $result=mysqli_query($con,$sql);
    $result2=mysqli_query($con,$sql2);
    if(sizeof($result)>0)
    {
        foreach($result as $resul)
        {
            foreach($resul as $r)
            {
                array_push($namedatearray,$r);
            }
        }
    }
    if(sizeof($result2)>0)
    {
        foreach($result2 as $resul)
        {
            foreach($resul as $r)
            {
                array_push($namedatearray,$r);
            }
        }
    }

    //calculation
    for($n=0;$n,$n<sizeof($namedatearray);$n+=2)
    {   
        $this->calculation($namedatearray[$n],$namedatearray[$n+1]);
    }

    $this->redirect(array(
                    'controller'=>'PortfolioManagement',
                    'action'=>'index'
                ) );
}

public function addRpm()
{
    set_time_limit(0);
    include("Component/connection.php");
    $name=$this->request->data['rpmtext'];
    $type=$this->request->data['selectBoxRpm'];
    $date=$this->request->data['datetextp'];
    $plist=$this->request->data['porttext'];
    $pweighting=$this->request->data['pweightingtextp'];
    $ilist=$this->request->data['indextextp'];
    $iweighting=$this->request->data['iweightingtextp'];

    $dateExplode = explode('/',$date);
    $year=$dateExplode[2];
    $month=$dateExplode[1];
    $day=$dateExplode[0];

    $date=$year."-".$month."-".$day;
    $name=$name." ".$type;
    $sql="insert into portfoliomanagement (date,portfolioname,fundlist,indexlist,fweightinglist,iweightinglist) values ('".$date."','".$name."','".$plist."','".$ilist."','".$pweighting."','".$iweighting."')";
    mysqli_query($con,$sql);

    //based on the new portfolio info, add to modelportfolios table.
    date_default_timezone_set('Australia/Melbourne');
    $date2 = date('d/m/Y', time());
    $dateExplode2 = explode('/',$date2);
    $latestyear=$dateExplode2[2];
    $latestmonth=$dateExplode2[1];
    if($year==$latestyear && $month<=$latestmonth)//same year, earlier month
    {
        $looptimes=$latestmonth-$month+1;
        for($n=0;$n<$looptimes;$n++)
        {
            $insertMonth=$month+$n;
            if($insertMonth==2){$insertDate=$year."-".$insertMonth."-"."28";}
            else{$insertDate=$year."-".$insertMonth."-"."30";}
            $sqlcheck="select count(*) from modelportfolios where portfolioname='".$name."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $resultcheck=mysqli_query($con,$sqlcheck);
            $countercheck=0;
            foreach($resultcheck as $recheck)
            {
                foreach($recheck as $rc)
                {
                    $countercheck=$rc;
                }
            }
            if($countercheck==0)
            {
                $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$plist."','".$ilist."','".$insertDate."','".$pweighting."','".$iweighting."')";
                mysqli_query($con,$sql3);
            }
        }
    }else if ($year<$latestyear)//earlier year
    {
        $yearlooptimes=$latestyear-$year+1;
        for($n=0;$n<$yearlooptimes;$n++)
        {
            $insertyear=$year+$n;
            if($n==0)
            {
                //first year
                $looptimes=12-$month+1;
                for($m=0;$m<$looptimes;$m++)
                {
                    $insertMonth=$month+$m;
                    if($insertMonth==2){$insertDate=$insertyear."-".$insertMonth."-"."28";}
                    else{$insertDate=$insertyear."-".$insertMonth."-"."30";}
                    $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$plist."','".$ilist."','".$insertDate."','".$pweighting."','".$iweighting."')";
                    mysqli_query($con,$sql3);
                }
            }
            else if ($n<$yearlooptimes-1)
            {
                // year bettween first and last year
                for($m=1;$m<=12;$m++)
                {
                    if($m==2){$insertDate=$insertyear."-".$m."-"."28";}
                    else{$insertDate=$insertyear."-".$m."-"."30";}
                    $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$plist."','".$ilist."','".$insertDate."','".$pweighting."','".$iweighting."')";
                    mysqli_query($con,$sql3);
                }
            }
            else
            {
                for($m=1;$m<=$latestmonth;$m++)
                {
                    if($m==2){$insertDate=$insertyear."-".$m."-"."28";}
                    else{$insertDate=$insertyear."-".$m."-"."30";}
                    $sql3="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$name."','".$plist."','".$ilist."','".$insertDate."','".$pweighting."','".$iweighting."')";
                    mysqli_query($con,$sql3);
                }
                //last year
            }
        }
    }

    //calculation if needed
    //may need to check if there's fund info in the db
    $sql4="select date from modelportfolios where portfolioname='".$name."' and year(date) ='".$year."' and month(date)>='".$month."'";
    $sql5="select date from modelportfolios where portfolioname='".$name."' and year(date) >'".$year."'";

    $resultdate2=mysqli_query($con,$sql5);
    $resultdate=mysqli_query($con,$sql4);
    foreach ($resultdate as $resultdat)
    {
        foreach ($resultdat as $rd)
        {
            $this->calculationrpm($name,$rd);
        }
    }

    foreach($resultdate2 as $resultdat2)
    {
        foreach ($resultdat2 as $rd2)
        {
            $this->calculationrpm($name,$rd2);
        }
    }

    $this->redirect(array(
                    'controller'=>'PortfolioManagement',
                    'action'=>'index'
                ) );
}

public function modifyRpm()
{
    set_time_limit(0);
    include("Component/connection.php");
    $name=$this->request->data['selectBoxRpm1'];
    $date=$this->request->data['datetextp1'];
    $plist=$this->request->data['porttext2'];
    $pweighting=$this->request->data['pweightingtextp1'];
    $ilist=$this->request->data['indextextp1'];
    $iweighting=$this->request->data['iweightingtextp1'];

    $dateExplode = explode('/',$date);
    $year=$dateExplode[2];
    $month=$dateExplode[1];
    $day=$dateExplode[0];

    $date=$year."-".$month."-".$day;

    //update portfoliomanagement table
    //insert into portfoliomanagment table
    $sql="select count(*) from PortfolioManagement where portfolioname ='".$name."' and year(date)='".$year."' and month(date)='".$month."' ";
    $resultexist=mysqli_query($con,$sql);
    $counterexist=0;
    foreach($resultexist as $re)
    {
        foreach($re as $r)
        {
            $counterexist=$r;
        }
    }
    echo $counterexist;
    if ($counterexist==0)
    {
        $sql="insert into portfoliomanagement (date,portfolioname,fundlist,indexlist,fweightinglist,iweightinglist) values ('".$date."','".$name."','".$plist."','".$ilist."','".$pweighting."','".$iweighting."')";
        mysqli_query($con,$sql);
    }
    //get all records id which date > modify date
    $sql="select id from portfoliomanagement where portfolioname='".$name."' and year(date)='".$year."' and month(date)>='".$month."' order by id asc";
    $sql2="select id from portfoliomanagement where portfolioname='".$name."' and year(date)>'".$year."' order by id asc";
    $result=mysqli_query($con,$sql);
    $result2=mysqli_query($con,$sql2);
    $updatedportid=array();
    if(sizeof($result)>0)
    {
        foreach($result as $resul)
        {
            foreach($resul as $r)
            {
                array_push($updatedportid,$r);
            }
        }
    }
    if(sizeof($result2)>0)
    {
        foreach($result2 as $resul)
        {
            foreach($resul as $r)
            {
                array_push($updatedportid,$r);
            }
        }
    }

    for($n=0;$n<sizeof($updatedportid);$n++)
    {
        $sql="update portfoliomanagement set fundlist='".$plist."',fweightinglist='".$pweighting."',indexlist='".$ilist."',iweightinglist='".$iweighting."' where id='".$updatedportid[$n]."'";
        mysqli_query($con,$sql);
    }
    //update portfolio table
    $sql="update modelportfolios set fundslist='".$plist."',fweightinglist='".$pweighting."',indexlist='".$ilist."',iweightinglist='".$iweighting."' where portfolioname='".$name."' and year(date)='".$year."' and month(date)>='".$month."' order by portfoliosid asc";
    $sql2="update modelportfolios set fundslist='".$plist."',fweightinglist='".$pweighting."',indexlist='".$ilist."',iweightinglist='".$iweighting."' where portfolioname='".$name."' and year(date)>'".$year."' order by portfoliosid asc";
    mysqli_query($con,$sql);
    mysqli_query($con,$sql2);

    //get modelportfolio names and dates
    $namedatearray=array();
    $sql="select portfolioname,date from modelportfolios where portfolioname='".$name."' and year(date)='".$year."' and month(date)>='".$month."' order by portfoliosid asc";
    $sql2="select portfolioname,date from modelportfolios where portfolioname='".$name."' and year(date)>'".$year."' order by portfoliosid asc";
    $result=mysqli_query($con,$sql);
    $result2=mysqli_query($con,$sql2);
    if(sizeof($result)>0)
    {
        foreach($result as $resul)
        {
            foreach($resul as $r)
            {
                array_push($namedatearray,$r);
            }
        }
    }
    if(sizeof($result2)>0)
    {
        foreach($result2 as $resul)
        {
            foreach($resul as $r)
            {
                array_push($namedatearray,$r);
            }
        }
    }

    //calculation
    for($n=0;$n,$n<sizeof($namedatearray);$n+=2)
    {   
        $this->calculationrpm($namedatearray[$n],$namedatearray[$n+1]);
    }

    $this->redirect(array(
                    'controller'=>'PortfolioManagement',
                    'action'=>'index'
                ) );
}

private function calculationrpm($name,$date)
{
    include("Component/connection.php");
    $dateExplode = explode('-',$date);
    $year=$dateExplode[0];
    $month=$dateExplode[1];
    $fundlist=array();
    $indexlist=array();
    $fundweightinglist=array();
    $indexweightinglist=array();

    //get fund and index info for portfolio
    $sql2="select fundslist,fweightinglist,indexlist,iweightinglist from modelportfolios where portfolioname='".$name."' and year(date)='".$year."' and month(date)='".$month."'";
    $result=mysqli_query($con,$sql2);
    $x=1;
    foreach($result as $resul)
    {
        foreach($resul as $re)
        {
            if($x==1)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundlist,$exploded);
                }
            }
            else if ($x==2)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundweightinglist,$exploded);
                }
            }
            else if ($x==3)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexlist,$exploded);
                }
            }
            else if ($x==4)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexweightinglist,$exploded);
                }
            }
            $x++;
        }
    }


    $sql="select PortfoliosID from modelportfolios where date<'".$date."' and portfolioname='".$name."'";
    $result=mysqli_query($con,$sql);
    $resultarray=array();
    foreach($result as $resul)
    {
        //$numberbefore=sizeof($resul);
        foreach ($resul as $r)
        {
            array_push($resultarray, $r);
        }
    }
    $numberbefore=sizeof($resultarray);
    //calc 1 month return
    if($numberbefore<2 && $numberbefore>=0)
    {
        $onemonthreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3 months
    else if ($numberbefore<5)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months
    else if ($numberbefore<11)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1 year
    else if ($numberbefore<23)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2 year
    else if ($numberbefore<35)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3 year
    else if ($numberbefore<47)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $threeyearreturn=$this->calcreturnrpm($name,$date,36);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4 year
    else if ($numberbefore<59)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $threeyearreturn=$this->calcreturnrpm($name,$date,36);
        $fouryearreturn=$this-> calcreturnrpm($name,$date,48);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4,5 year
    else if ($numberbefore)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;
        $fiveyearreturn=0;

        $onemonthreturn=$this->calcreturnrpm($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturnrpm($name,$date,3);
        $sixmonthreturn=$this->calcreturnrpm($name,$date,6);
        $oneyearreturn=$this->calcreturnrpm($name,$date,12);
        $twoyearreturn=$this->calcreturnrpm($name,$date,24);
        $threeyearreturn=$this->calcreturnrpm($name,$date,36);
        $fouryearreturn=$this-> calcreturnrpm($name,$date,48);
        $fiveyearreturn=$this-> calcreturnrpm($name,$date,60);
        $sinceinception=$this->calcreturnrpm($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturnrpm($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',fiveyearreturn='".$fiveyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
}
private function calculation($name,$date)//$name is portfolio name 
{
    include("Component/connection.php");
    $dateExplode = explode('-',$date);
    $year=$dateExplode[0];
    $month=$dateExplode[1];
    $fundlist=array();
    $indexlist=array();
    $fundweightinglist=array();
    $indexweightinglist=array();

    //get fund and index info for portfolio
    $sql2="select fundslist,fweightinglist,indexlist,iweightinglist from modelportfolios where portfolioname='".$name."' and year(date)='".$year."' and month(date)='".$month."'";
    $result=mysqli_query($con,$sql2);
    $x=1;
    foreach($result as $resul)
    {
        foreach($resul as $re)
        {
            if($x==1)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundlist,$exploded);
                }
            }
            else if ($x==2)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($fundweightinglist,$exploded);
                }
            }
            else if ($x==3)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexlist,$exploded);
                }
            }
            else if ($x==4)
            {
                $explodes=explode(',',$re);
                foreach($explodes as $exploded)
                {
                    array_push($indexweightinglist,$exploded);
                }
            }
            $x++;
        }
    }


    $sql="select PortfoliosID from modelportfolios where date<'".$date."' and portfolioname='".$name."'";
    $result=mysqli_query($con,$sql);
    $resultarray=array();
    foreach($result as $resul)
    {
        //$numberbefore=sizeof($resul);
        foreach ($resul as $r)
        {
            array_push($resultarray, $r);
        }
    }
    $numberbefore=sizeof($resultarray);
    //calc 1 month return
    if($numberbefore<2 && $numberbefore>=0)
    {
        $onemonthreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3 months
    else if ($numberbefore<5)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months
    else if ($numberbefore<11)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',sinceinceptionreturn='".$sinceinception."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1 year
    else if ($numberbefore<23)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2 year
    else if ($numberbefore<35)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3 year
    else if ($numberbefore<47)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $threeyearreturn=$this->calcreturn($name,$date,36);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4 year
    else if ($numberbefore<59)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $threeyearreturn=$this->calcreturn($name,$date,36);
        $fouryearreturn=$this-> calcreturn($name,$date,48);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
    //calc 1,3,6 months and 1,2,3,4,5 year
    else if ($numberbefore)
    {
        $onemonthreturn=0;
        $threemonthreturn=0;
        $sixmonthreturn=0;
        $oneyearreturn=0;
        $twoyearreturn=0;
        $threeyearreturn=0;
        $fouryearreturn=0;
        $fiveyearreturn=0;

        $onemonthreturn=$this->calcreturn($name,$date,1);
        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
        $threemonthreturn=$this->calcreturn($name,$date,3);
        $sixmonthreturn=$this->calcreturn($name,$date,6);
        $oneyearreturn=$this->calcreturn($name,$date,12);
        $twoyearreturn=$this->calcreturn($name,$date,24);
        $threeyearreturn=$this->calcreturn($name,$date,36);
        $fouryearreturn=$this-> calcreturn($name,$date,48);
        $fiveyearreturn=$this-> calcreturn($name,$date,60);
        $sinceinception=$this->calcreturn($name,$date,'sinceinception');
        $sinceinceptiona=$this->calcreturn($name,$date,'sinceinceptiona');

        $sqlupdate="update modelportfolios set onemonthreturn='".$onemonthreturn."',threemonthreturn='".$threemonthreturn."',sixmonthreturn='".$sixmonthreturn."',oneyearreturn='".$oneyearreturn."',twoyearreturn='".$twoyearreturn."',threeyearreturn='".$threeyearreturn."',fouryearreturn='".$fouryearreturn."',fiveyearreturn='".$fiveyearreturn."',sinceinceptionreturn='".$sinceinception."',sinceinceptionreturnc='".$sinceinceptiona."' where date='".$date."' and portfolioname='".$name."'";
        mysqli_query($con,$sqlupdate);
    }
}

private function calcreturn($name,$date,$limit)
{   
    include("Component/connection.php");
    if($limit == 1)
    {
        $sql3="select fundslist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";
        $temp=mysqli_query($con,$sql3);
        $return=1;
        $fundarray=array();
        $fundweightingarray=array();
        $fundvaluearray=array();
        foreach ($temp as $tem)
        {
            foreach($tem as $t)
            {
                $fundExplode = explode(',',$t);
                foreach($fundExplode as $fe)
                {
                    array_push($fundarray, $fe);
                }
            }
        }
        for($n=0;$n<sizeof($fundarray);$n++)
        {
            $sql4="select onemonthreturn from managedfunds where fundname='".$fundarray[$n]."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $result4=mysqli_query($con,$sql4);
            foreach($result4 as $resul)
            {
                foreach($resul as $t)
                {
                    array_push($fundvaluearray,$t);
                }
            }
        }
        $sql5="select fweightinglist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";

        $result5=mysqli_query($con,$sql5);
        foreach($result5 as $resul)
        {
            foreach($resul as $r)
            {
                $weightingExplode=explode(',',$r);
                foreach($weightingExplode as $t)
                {
                    array_push($fundweightingarray,$t);
                }
            }
        }
        for($m=0;$m<sizeof($fundvaluearray);$m++)
        {
            $return+=$fundvaluearray[$m]*$fundweightingarray[$m];
        }   
        $return-=1;
        return $return;
    }
    else if ($limit != 'sinceinception' && $limit != 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and date<='".$date."' order by date desc limit ".$limit;
        $temp=mysqli_query($con,$sql3);
        $return=1;

        $counter=0;
        foreach($temp as $tem)
        {
            foreach ($tem as $t)
            {
                $return=$return*($t+1);
                $counter++;
            }
        }
        if($counter>12)
        {
            $exp=12/$counter;

            $return=pow($return,$exp)-1;
        }
        else
        {
            $return-=1;
        }
        return $return;
        
    }
    else if ($limit == 'sinceinception')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }
        for($n=0;$n<sizeof($returnarray);$n++)
        {
            $return=$return*(1+$returnarray[$n]);
        }
        $return-=1;

        return $return;
    }
    else if ($limit == 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }

        if(sizeof($returnarray)>11)
        {
            for($n=0;$n<sizeof($returnarray);$n++)
            {
                $return=$return*(1+$returnarray[$n]);
        }

            $exp=12/sizeof($returnarray);

            $return=pow($return,$exp)-1;
        }
        return $return;
    }
}

private function calcreturnrpm($name,$date,$limit)
{
    include("Component/connection.php");
    if($limit == 1)
    {
        $sql3="select fundslist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";
        $temp=mysqli_query($con,$sql3);
        $return=1;
        $fundarray=array();
        $fundweightingarray=array();
        $fundvaluearray=array();
        foreach ($temp as $tem)
        {
            foreach($tem as $t)
            {
                $fundExplode = explode(',',$t);
                foreach($fundExplode as $fe)
                {
                    array_push($fundarray, $fe);
                }
            }
        }
        //fweitinglist store portfolionames for RPM
        for($n=0;$n<sizeof($fundarray);$n++)
        {
            $sql4="select onemonthreturn from modelportfolios where portfolioname='".$fundarray[$n]."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $result4=mysqli_query($con,$sql4);
            foreach($result4 as $resul)
            {
                foreach($resul as $t)
                {
                    array_push($fundvaluearray,$t);
                }
            }
        }
        //get weightings for RPM
        $sql5="select fweightinglist from modelportfolios where portfolioname ='".$name."' and date ='".$date."' order by date asc limit 1";

        $result5=mysqli_query($con,$sql5);
        foreach($result5 as $resul)
        {
            foreach($resul as $r)
            {
                $weightingExplode=explode(',',$r);
                foreach($weightingExplode as $t)
                {
                    array_push($fundweightingarray,$t);
                }
            }
        }
        for($m=0;$m<sizeof($fundvaluearray);$m++)
        {
            $return+=$fundvaluearray[$m]*$fundweightingarray[$m];
        }   
        $return-=1;
        return $return;
    }
    else if ($limit != 'sinceinception' && $limit != 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and date<='".$date."' order by date desc limit ".$limit;
        $temp=mysqli_query($con,$sql3);
        $return=1;

        $counter=0;
        foreach($temp as $tem)
        {
            foreach ($tem as $t)
            {
                $return=$return*($t+1);
                $counter++;
            }
        }
        if($counter>12)
        {
            $exp=12/$counter;

            $return=pow($return,$exp)-1;
        }
        else
        {
            $return-=1;
        }
        return $return;
        
    }
    else if ($limit == 'sinceinception')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }
        for($n=0;$n<sizeof($returnarray);$n++)
        {
            $return=$return*(1+$returnarray[$n]);
        }
        $return-=1;

        return $return;
    }
    else if ($limit == 'sinceinceptiona')
    {
        $sql3="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)<year('".$date."') order by date desc";
        $sql4="select onemonthreturn from modelportfolios where portfolioname ='".$name."' and year(date)=year('".$date."') and month(date)<=month('".$date."') order by date desc";
        $resultBefore=mysqli_query($con,$sql3);
        $resultCurrent=mysqli_query($con,$sql4);
        $return=1;
        $returnarray=array();
        foreach($resultBefore as $resulB)
        {
            foreach($resulB as $rb)
            {
                array_push($returnarray,$rb);
            }
        }

        foreach($resultCurrent as $resulC)
        {
            foreach($resulC as $rc)
            {
                array_push($returnarray,$rc);
            }
        }

        if(sizeof($returnarray)>11)
        {
            for($n=0;$n<sizeof($returnarray);$n++)
            {
                $return=$return*(1+$returnarray[$n]);
        }

            $exp=12/sizeof($returnarray);

            $return=pow($return,$exp)-1;
        }
        return $return;
    }
}
}

